ALTER TABLE Setting ADD UNIQUE (Name)
GO
ALTER TABLE [SystemCenter.Setting] ADD UNIQUE (Name)
GO
ALTER TABLE [MiMD.Setting] ADD UNIQUE (Name)
GO

DECLARE @DF_FileGroup_Error NVARCHAR(100)
SELECT @DF_FileGroup_Error = CONCAT
(
    'ALTER TABLE FileGroup DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('FileGroup') AND
            sys.columns.name = 'Error'
    )
)
EXECUTE sp_executesql @DF_FileGroup_Error
GO
ALTER TABLE FileGroup
	DROP COLUMN Error
GO

ALTER TABLE FileGroup
ADD ProcessingStatus INT NOT NULL DEFAULT(0)
GO


ALTER TABLE ChannelTemplateFile ADD
	ShowTrend BIT NOT NULL DEFAULT(1),
	ShowEvents BIT NOT NULL DEFAULT(1),
	SortOrder INT NOT NULL DEFAULT(0)
GO


INSERT INTO NodeType VALUES('Grafana', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.Grafana.GrafanaHostingNode')
GO


ALTER TABLE Node ADD AssignedHostRegistrationID INT NULL REFERENCES HostRegistration(ID)
GO


CREATE TABLE GenerationAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
)
GO

CREATE TABLE StationAuxAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
)
GO

CREATE TABLE StationBatteryAttributes
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
)
GO

-- Generation Model
CREATE VIEW Generation AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN GenerationAttributes ON Asset.ID = GenerationAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Generation ON GENERATION
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'Generation') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO GenerationAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Generation ON GENERATION
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Generation Model Triggers 
-- Station Auxilary Model
CREATE VIEW StationAux AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN StationAuxAttributes ON Asset.ID = StationAuxAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Aux ON STATIONAUX
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'StationAux') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO StationAuxAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Aux ON STATIONAUX
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Station Auxilary Model Triggers 
-- Station Battery Model
CREATE VIEW StationBattery AS
	SELECT 
		AssetID AS ID,
		AssetKey,
		VoltageKV,
		Description,
		AssetName,
		AssetTypeID,
		Spare
	FROM Asset JOIN StationBatteryAttributes ON Asset.ID = StationBatteryAttributes.AssetID
GO

CREATE TRIGGER TR_INSERT_Battery ON STATIONBATTERY
INSTEAD OF INSERT AS 
BEGIN
	INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
		SELECT 
			AssetKey AS AssetKey,
			(SELECT ID FROM AssetType WHERE Name = 'StationBattery') AS AssetTypeID,
			Description AS Description,
			AssetName AS AssetName,
			VoltageKV AS VoltageKV,
			Spare AS Spare
	FROM INSERTED

	INSERT INTO StationBatteryAttributes (AssetID)
		SELECT 
			(SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID
	FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_Battery ON STATIONBATTERY
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
END
GO

-- END Station Bettery Model Triggers


DECLARE @FK_PQMeasurement_UnitID NVARCHAR(100)
SELECT @FK_PQMeasurement_UnitID = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'UnitID' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_UnitID
GO

DECLARE @FK_PQMeasurement_PhaseID NVARCHAR(100)
SELECT @FK_PQMeasurement_PhaseID = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT CONSTRAINT_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE
            COLUMN_NAME = 'PhaseID' AND
			TABLE_NAME = 'PQMeasurement'
    )
)
EXECUTE sp_executesql @FK_PQMeasurement_PhaseID
GO

DECLARE @DF_PQMeas_HG NVARCHAR(100)
SELECT @DF_PQMeas_HG = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('PQMeasurement') AND
            sys.columns.name = 'HarmonicGroup'
    )
)
EXECUTE sp_executesql @DF_PQMeas_HG
GO

DECLARE @DF_PQMeas_Enabled NVARCHAR(100)
SELECT @DF_PQMeas_Enabled = CONCAT
(
    'ALTER TABLE PQMeasurement DROP CONSTRAINT ',
    (
        SELECT sys.default_constraints.name 
        FROM
            sys.default_constraints JOIN
            sys.columns ON sys.columns.default_object_id = sys.default_constraints.object_id
        WHERE
            sys.default_constraints.parent_object_id = object_id('PQMeasurement') AND
            sys.columns.name = 'Enabled'
    )
)
EXECUTE sp_executesql @DF_PQMeas_Enabled
GO

--Separated these out since GPC had some unanticipated default constraints to deal with individually and it makes it easier to ID them if they're separate queries.
ALTER TABLE PQMeasurement DROP COLUMN UnitID
GO
ALTER TABLE PQMeasurement DROP COLUMN PhaseID
GO
ALTER TABLE PQMeasurement DROP COLUMN HarmonicGroup
GO
ALTER TABLE PQMeasurement DROP COLUMN Enabled
GO

ALTER TABLE PQMeasurement ADD Unit VARCHAR(200) NOT NULL DEFAULT('None')
GO

DROP TABLE Unit
GO

DROP TABLE PQTrendStat
GO

DROP TABLE StepChangeStat
GO

DROP TABLE StepChangeMeasurement
GO

CREATE NONCLUSTERED INDEX IX_PQMeasurement_MeasurementTypeID
ON PQMeasurement(MeasurementTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_PQMeasurement_MeasurementCharacteristicID
ON PQMeasurement(MeasurementCharacteristicID ASC)
GO


CREATE TABLE MeterDependentAssetDesignation
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterAssetID INT NOT NULL REFERENCES MeterAsset(ID),
    Designation VARCHAR(200) NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_MeterDependentAssetDesignation_MeterAssetID
ON MeterDependentAssetDesignation(MeterAssetID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultDetectionLogic_MeterAssetID
ON FaultDetectionLogic(MeterAssetID ASC)
GO


ALTER TABLE AlarmLog ADD SeverityID INT NOT NULL REFERENCES AlarmSeverity(ID) DEFAULT(4)
GO

CREATE TABLE LatestAlarmLog
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AlarmID INT NOT NULL UNIQUE REFERENCES Alarm(ID),
    AlarmLogID INT NOT NULL REFERENCES AlarmLog(ID),
    SeverityID INT NOT NULL REFERENCES AlarmSeverity(ID),
    StartTime DATETIME NOT NULL,
    EndTime DATETIME NULL
)
GO

CREATE NONCLUSTERED INDEX IX_AlarmGroup_AlarmTypeID
ON AlarmGroup(AlarmTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmGroup_SeverityID
ON AlarmGroup(SeverityID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Alarm_AlarmGroupID
ON Alarm(AlarmGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Alarm_SeriesID
ON Alarm(SeriesID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmFactor_AlarmGroupID
ON AlarmFactor(AlarmGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmFactor_SeverityID
ON AlarmFactor(SeverityID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmDayGroupAlarmDay_AlarmDayID
ON AlarmDayGroupAlarmDay(AlarmDayID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmDayGroupAlarmDay_AlarmDayGroupID
ON AlarmDayGroupAlarmDay(AlarmDayGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_AlarmID
ON LatestAlarmLog(AlarmID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LatestAlarmLog_SeverityID
ON LatestAlarmLog(SeverityID ASC)
GO

CREATE TRIGGER AlarmLog_UpdateLatestAlarmLog
    ON AlarmLog
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Update existing records in LatestAlarmLog
    UPDATE LatestAlarmLog
    SET
        AlarmLogID = inserted.ID,
        SeverityID = inserted.SeverityID,
        StartTime = inserted.StartTime,
        EndTime = inserted.EndTime
    FROM
        LatestAlarmLog JOIN
        inserted ON inserted.AlarmID = LatestAlarmLog.AlarmID JOIN
        AlarmLog ON LatestAlarmLog.AlarmLogID = AlarmLog.ID
    WHERE inserted.StartTime >= AlarmLog.StartTime

    -- Determine which alarms have never had logs before now
    SELECT DISTINCT inserted.AlarmID
    INTO #alarm
    FROM
        inserted LEFT OUTER JOIN
        LatestAlarmLog ON inserted.AlarmID = LatestAlarmLog.AlarmID
    WHERE LatestAlarmLog.ID IS NULL

    -- Insert new records in LatestAlarmLog
    -- for alarms that never had logs before now
    INSERT INTO LatestAlarmLog(AlarmID, AlarmLogID, SeverityID, StartTime, EndTime)
    SELECT
        AlarmLog.AlarmID,
        AlarmLog.ID AlarmLogID,
        AlarmLog.SeverityID,
        AlarmLog.StartTime,
        AlarmLog.EndTime
    FROM
        #alarm CROSS APPLY
        (
            SELECT TOP 1 *
            FROM inserted
            WHERE AlarmID = #alarm.AlarmID
            ORDER BY StartTime DESC, ID DESC
        ) AlarmLog
END
GO

DROP VIEW AlarmGroupView
GO

CREATE VIEW AlarmGroupView AS 
SELECT 
	AlarmGroup.ID,
	AlarmGroup.Name,
	AlarmSeverity.Name AlarmSeverity,
	CountStats.ChannelCount Channels,
	CountStats.MeterCount Meters,
	LastAlarm.StartTime LastAlarmStart,
	LastAlarm.EndTime LastAlarmEnd,
	LastAlarm.ChannelName LastChannel,
	LastAlarm.MeterName LastMeter,
	AlarmType.Description AS AlarmType
FROM 
	AlarmGroup LEFT JOIN
	AlarmSeverity ON AlarmGroup.SeverityID = AlarmSeverity.ID LEFT JOIN
	AlarmType ON AlarmGroup.AlarmTypeID = AlarmType.ID OUTER APPLY
    (
        SELECT
            COUNT(DISTINCT Channel.ID) ChannelCount,
            COUNT(DISTINCT Channel.MeterID) MeterCount
        FROM
            Channel JOIN
            Series ON Series.ChannelID = Channel.ID JOIN
            Alarm ON Alarm.SeriesID = Series.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
    ) CountStats OUTER APPLY
    (
        SELECT TOP 1
            LatestAlarmLog.StartTime,
            LatestAlarmLog.EndTime,
            Channel.Name ChannelName,
            Meter.Name MeterName
        FROM
            Alarm JOIN
            Series ON Alarm.SeriesID = Series.ID JOIN
            Channel ON Series.ChannelID = Channel.ID JOIN
            Meter ON Channel.MeterID = Meter.ID JOIN
            LatestAlarmLog ON LatestAlarmLog.AlarmID = Alarm.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
        ORDER BY
            LatestAlarmLog.StartTime DESC,
            LatestAlarmLog.AlarmLogID DESC
    ) LastAlarm
GO

DROP VIEW ActiveAlarmView
GO

CREATE VIEW ActiveAlarmView AS
SELECT
    Alarm.ID AS AlarmID,
    Alarm.AlarmGroupID AS AlarmGroupID,
    AlarmGroup.AlarmTypeID AS AlarmTypeID,
    AlarmFactor.ID AS AlarmFactorID,
    AlarmFactor.SeverityID,
    Alarm.SeriesID AS SeriesID,
    AlarmFactor.Factor AS Value
FROM
    (
        SELECT ID, Factor, AlarmGroupID, SeverityID
        FROM AlarmFactor
        UNION
        SELECT
            NULL AS ID,
            1.0 AS Factor,
            AlarmGroup.ID AS AlarmGroupID,
            AlarmGroup.SeverityID
        FROM AlarmGroup
    ) AlarmFactor LEFT JOIN
    Alarm ON AlarmFactor.AlarmGroupID = alarm.AlarmGroupID LEFT JOIN
    AlarmGroup ON Alarm.AlarmGroupID = AlarmGroup.ID
GO

CREATE FUNCTION RecursiveMeterSearch(@assetGroupID int)
RETURNS TABLE
AS
RETURN
    WITH AssetGroupHierarchy AS
    (
        SELECT
            AssetGroupAssetGroup.ParentAssetGroupID,
            AssetGroupAssetGroup.ChildAssetGroupID,
            1 Depth
        FROM AssetGroupAssetGroup
        WHERE AssetGroupAssetGroup.ParentAssetGroupID = @assetGroupID
        UNION ALL
        SELECT
            AssetGroupHierarchy.ParentAssetGroupID,
            AssetGroupAssetGroup.ChildAssetGroupID,
            AssetGroupHierarchy.Depth + 1 Depth
        FROM
            AssetGroupHierarchy JOIN
            AssetGroupAssetGroup ON AssetGroupHierarchy.ChildAssetGroupID = AssetGroupAssetGroup.ParentAssetGroupID
        WHERE AssetGroupHierarchy.Depth < 10
    )
    SELECT DISTINCT MeterID AS ID
    FROM
        MeterAssetGroup LEFT JOIN
        AssetGroupHierarchy ON MeterAssetGroup.AssetGroupID = AssetGroupHierarchy.ChildAssetGroupID
    WHERE
        MeterAssetGroup.AssetGroupID = @assetGroupID OR
        MeterAssetGroup.AssetGroupID IN (SELECT ChildAssetGroupID FROM AssetGroupHierarchy)
GO


ALTER TABLE StandardMagDurCurve
	DROP COLUMN
	XHigh,
	XLow,
	YHigh,
	YLow,
	UpperCurve,
	LowerCurve
GO

ALTER TABLE StandardMagDurCurve
	ADD
		Color VARCHAR(255) NOT NULL DEFAULT('#007a29')
GO



INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.AdminUser', 'admin', 'admin'
WHERE 'Grafana.AdminUser' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.AuthProxyHeaderName', 'X-WEBAUTH-USER', 'X-WEBAUTH-USER'
WHERE 'Grafana.AuthProxyHeaderName' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.BasePath', 'Grafana', 'Grafana'
WHERE 'Grafana.BasePath' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.HostedURL', 'http://localhost:8185', 'http://localhost:8185'
WHERE 'Grafana.HostedURL' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.InitializationTimeout', '30', '30'
WHERE 'Grafana.InitializationTimeout' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.LastDashboardCookieName', 'x-last-dashboard', 'x-last-dashboard'
WHERE 'Grafana.LastDashboardCookieName' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.OrganizationID', '1', '1'
WHERE 'Grafana.OrganizationID' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'Grafana.ServerPath', 'Grafana\bin\grafana-server.exe', 'Grafana\bin\grafana-server.exe'
WHERE 'Grafana.ServerPath' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'FileProcessor.FileGroupingPattern', '^(?<FileGroupKey>.*)\.[^\.]*$', '^(?<FileGroupKey>.*)\.[^\.]*$'
WHERE 'FileProcessor.FileGroupingPattern' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'HIDS.HistogramBucket', 'histogram_bucket', 'histogram_bucket'
WHERE 'HIDS.HistogramBucket' NOT IN (SELECT Name FROM Setting)
GO

INSERT INTO Setting(Name, Value, DefaultValue) SELECT 'EventEmail.RestorationURL', 'http://localhost:8989/RestoreEventEmail.cshtml', 'http://localhost:8989/RestoreEventEmail.cshtml'
WHERE 'EventEmail.RestorationURL' NOT IN (SELECT Name FROM Setting)
GO

UPDATE StandardMagDurCurve
SET Color = '#007a29'
WHERE Name = 'ITIC'
GO

UPDATE StandardMagDurCurve
SET Color = '#edc240'
WHERE Name = 'SEMI F47'
GO

UPDATE StandardMagDurCurve
SET Color = '#a30000'
WHERE Name = 'IEEE 1668 Type I & II'
GO

UPDATE StandardMagDurCurve
SET Color = '#185aa9'
WHERE Name = 'IEEE 1668 Type III'
GO

UPDATE StandardMagDurCurve
SET Color = '#d3d3d3'
WHERE Name = 'NERC PRC-024-2'
GO

INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Transients', NULL, '#afd8f8')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Instantaneous Sag', NULL, '#f47d23')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Instantaneous Swell', NULL, '#008c48')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Mom. Interruption', NULL, '#ee2e2f')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Momentary Sag', NULL, '#737373')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Momentary Swell', NULL, '#662c91')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temp. Interruption', NULL, '#bd9b33')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temporary Sag', NULL, '#ff904f')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Temporary Swell', NULL, '#ff9999')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Sustained Int.', NULL, '#0029A3')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Undervoltage', NULL, '#cb4b4b')
GO
INSERT StandardMagDurCurve (Name, Area, Color) VALUES (N'IEEE 1159 Overvoltage', NULL, '#4da74d')
GO

UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.000001 0, 0.01 0, 0.01 5.0, 0.000001 5.0, 0.000001 0))' WHERE Name = 'IEEE 1159 Transients'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0.1, 0.5 0.1, 0.5 0.9, 0.01 0.9, 0.01 0.1))' WHERE Name = 'IEEE 1159 Instantaneous Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 1.1, 0.5 1.1, 0.5 1.8, 0.01 1.8, 0.01 1.1))' WHERE Name = 'IEEE 1159 Instantaneous Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0, 3.0 0, 3.0 0.1, 0.01 0.1, 0.01 0))' WHERE Name = 'IEEE 1159 Mom. Interruption'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.5 0.1, 3.0 0.1, 3 0.9, 0.5 0.9, 0.5 0.1))' WHERE Name = 'IEEE 1159 Momentary Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.5 1.1, 3.0 1.1, 3.0 1.4, 0.5 1.4, 0.5 1.1))' WHERE Name = 'IEEE 1159 Momentary Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 0, 60.0 0, 60.0 0.1, 3.0 0.1, 3.0 0))' WHERE Name = 'IEEE 1159 Temp. Interruption'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 0.1, 60.0 0.1, 60.0 0.9, 3.0 0.9, 3.0 0.1))' WHERE Name = 'IEEE 1159 Temporary Sag'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((3.0 1.1, 60.0 1.1, 60.0 1.2, 3.0 1.2, 3.0 1.1))' WHERE Name = 'IEEE 1159 Temporary Swell'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 0, 1000.0 0, 1000.0 0.1, 60.0 0.1, 60.0 0))' WHERE Name = 'IEEE 1159 Sustained Int.'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 0.8, 1000.0 0.8, 1000.0 0.9, 60.0 0.9, 60.0 0.8))' WHERE Name = 'IEEE 1159 Undervoltage'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((60.0 1.1, 1000.0 1.1, 1000.0 1.2, 60.0 1.2, 60.0 1.1))' WHERE Name = 'IEEE 1159 Overvoltage'
GO

DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Enabled'
GO

DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Frequency'
GO

DELETE FROM Setting WHERE Name = 'PQTrendingWebReport.Verbose'
GO

DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Enabled'
GO

DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Frequency'
GO

DELETE FROM Setting WHERE Name = 'StepChangeWebReport.Verbose'
GO

INSERT INTO TriggeredEmailDatasource (Name, AssemblyName, TypeName,ConfigUI) VALUES
('FTT','openXDA.NotificationDataSources.dll','openXDA.NotificationDataSources.FaultTraceTool.FTTDataSource','ftt')
GO


INSERT INTO AssetType (ID, Name, Description) VALUES (9,'StationAux','Station Auxilary')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (10,'StationBattery','Station Battery')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (11,'Generation','Generation')
GO

TRUNCATE TABLE PQMeasurement
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('RMS Voltage', 'RMS Voltage', 'Volts', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'RMS'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Unbalance(S2/S1)', 'Voltage Unbalance(S2/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Unbalance(S0/S1)', 'Voltage Unbalance(S0/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Flicker Pst', 'Short Term Flicker Perceptibility (Pst)', 'Per Unit', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'FlkrPST'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage THD', 'Voltage THD', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Voltage Harmonic', 'Voltage Harmonic', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Voltage'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Unbalance(S2/S1)', 'Current Unbalance(S2/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S2S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Unbalance(S0/S1)', 'Current Unbalance(S0/S1)', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S0S1'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current THD', 'Current THD', 'Amps', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'TotalTHD'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Current Harmonic', 'Current Harmonic', 'Amps', (SELECT ID FROM MeasurementType WHERE Name = 'Current'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'SpectraHGroup'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Active Power', 'Active Power', 'KW', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'P'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Reactive Power', 'Reactive Power', 'KVAR', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'Q'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Apparent Power', 'Apparent Power', 'KVA', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'S'))
GO
INSERT INTO PQMeasurement(Name, Description, Unit, MeasurementTypeID, MeasurementCharacteristicID) VALUES('Power Factor', 'Power Factor', 'Percent', (SELECT ID FROM MeasurementType WHERE Name = 'Power'), (SELECT ID FROM MeasurementCharacteristic WHERE Name = 'PF'))
GO

INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'FAWG.Enabled', 'False', 'False'
WHERE 'FAWG.Enabled' NOT IN (SELECT Name FROM [SystemCenter.Setting])
GO

CREATE Table [ExternalDatabases] (
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    Schedule varchar(50) NULL DEFAULT(NULL),
    ConnectionString Varchar(MAX) NOT NULL,
    DataProviderString VARCHAR(MAX) NULL,
    Encrypt bit NOT NULL DEFAULT(0),
    LastDataUpdate DATETIME2 NULL DEFAULT(NULL),
    Constraint UC_ExternalDatabase UNIQUE(Name)
)

ALTER TABLE ExternalOpenXDAField
ADD ExternalDBTableID INT NOT NULL FOREIGN KEY REFERENCES [extDBTables](ID)
GO

ALTER TABLE LocationDrawing ADD
	Number VARCHAR(200) NULL,
	Category VARCHAR(max) NULL,
	CONSTRAINT UC_SystemCenter_LocationDrawing_Number UNIQUE (Number)
GO

INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'MiMD.Url', 'http://localhost:8989', ''
WHERE 'MiMD.Url' NOT IN (SELECT Name FROM [SystemCenter.Setting])
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'MiMD.APIKey', '', ''
WHERE 'MiMD.APIKey' NOT IN (SELECT Name FROM [SystemCenter.Setting])
GO
INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'MiMD.APIToken', '', ''
WHERE 'MiMD.APIToken' NOT IN (SELECT Name FROM [SystemCenter.Setting])
GO

CREATE TRIGGER [dbo].[UO_AdditonaFieldValue] 
ON [dbo].[AdditionalFieldValue]
AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	UPDATE original SET UpdatedOn = SYSDATETIME()
		FROM dbo.[AdditionalFieldValue] as original INNER JOIN deleted ON 
		original.ID = deleted.ID AND 
		(
			original.Value <> deleted.Value OR
			(original.Value IS NULL AND deleted.Value IS NOT NULL) OR
			(original.Value IS NOT NULL AND deleted.Value IS NULL)
		);
END
GO

INSERT INTO [SystemCenter.Setting](Name, Value, DefaultValue) SELECT 'PQBrowser.Url', 'http://localhost', 'http://localhost'
WHERE 'PQBrowser.Url' NOT IN (SELECT Name FROM [SystemCenter.Setting])
GO


ALTER TABLE [SEBrowser.Widget]
DROP COLUMN Enabled
GO

ALTER TABLE [SEBrowser.Widget]
ADD Type VARCHAR(max) NOT NULL DEFAULT('OpenSEE')
GO

UPDATE [SEBrowser.Widget] SET Name = 'OpenSEE' WHERE Name = 'EventSearchOpenSEE'
GO
UPDATE [SEBrowser.Widget] SET Type = 'OpenSEE' WHERE Name = 'OpenSEE'
GO

UPDATE [SEBrowser.Widget] SET Name = 'FaultSegments' WHERE Name = 'EventSearchFaultSegments'
GO
UPDATE [SEBrowser.Widget] SET Type = 'EventSearchFaultSegments' WHERE Name = 'FaultSegments'
GO

UPDATE [SEBrowser.Widget] SET Name = 'VoltageDisturbances' WHERE Name = 'EventSearchAssetVoltageDisturbances'
GO
UPDATE [SEBrowser.Widget] SET Type = 'VoltageDisturbances' WHERE Name = 'VoltageDisturbances'
GO

UPDATE [SEBrowser.Widget] SET Name = 'ESRIMap' WHERE Name = 'TVAESRIMap'
GO
UPDATE [SEBrowser.Widget] SET Type = 'ESRIMap' WHERE Name = 'ESRIMap'
GO

UPDATE [SEBrowser.Widget] SET Name = 'FaultInfo' WHERE Name = 'TVAFaultInfo'
GO
UPDATE [SEBrowser.Widget] SET Type = 'FaultInfo' WHERE Name = 'FaultInfo'
GO

UPDATE [SEBrowser.Widget] SET Type = 'LineParameters' WHERE Name = 'LineParameters'
GO

UPDATE [SEBrowser.Widget] SET Name = 'Lightning' WHERE Name = 'TVALightning'
GO
UPDATE [SEBrowser.Widget] SET Type = 'Lightning' WHERE Name = 'Lightning'
GO

UPDATE [SEBrowser.Widget] SET Name = 'StructureInfo' WHERE Name = 'TVAStructureInfo'
GO
UPDATE [SEBrowser.Widget] SET Type = 'StructureInfo' WHERE Name = 'StructureInfo'
GO

UPDATE [SEBrowser.Widget] SET Type = 'AssetHistoryTable' WHERE Name = 'AssetHistoryTable'
GO

UPDATE [SEBrowser.Widget] SET Type = 'AssetHistoryStats' WHERE Name = 'AssetHistoryStats'
GO

UPDATE [SEBrowser.Widget] SET Name = 'CorrelatedSags' WHERE Name = 'EventSearchCorrelatedSags'
GO
UPDATE [SEBrowser.Widget] SET Type = 'CorrelatedSags' WHERE Name = 'CorrelatedSags'
GO

UPDATE [SEBrowser.Widget] SET Type = 'TVASIDA' WHERE Name = 'TVASIDA'
GO

UPDATE [SEBrowser.Widget] SET Name = 'SOE' WHERE Name = 'TVASOE'
GO
UPDATE [SEBrowser.Widget] SET Type = 'SOE' WHERE Name = 'SOE'
GO

UPDATE [SEBrowser.Widget] SET Type = 'TVASLC' WHERE Name = 'TVASLC'
GO

UPDATE [SEBrowser.Widget] SET Type = 'TVAPQWeb' WHERE Name = 'TVAPQWeb'
GO

UPDATE [SEBrowser.Widget] SET Name = 'InterruptionReport' WHERE Name = 'HECCOIR'
GO
UPDATE [SEBrowser.Widget] SET Type = 'InterruptionReport' WHERE Name = 'InterruptionReport'
GO

UPDATE [SEBrowser.Widget] SET Type = 'pqi' WHERE Name = 'pqi'
GO

UPDATE [SEBrowser.Widget] SET Name = 'FileInfo' WHERE Name = 'EventSearchFileInfo'
GO
UPDATE [SEBrowser.Widget] SET Type = 'FileInfo' WHERE Name = 'FileInfo'
GO

UPDATE [SEBrowser.Widget] SET Name = 'Notes' WHERE Name = 'EventSearchNoteWindow'
GO
UPDATE [SEBrowser.Widget] SET Type = 'Notes' WHERE Name = 'Notes'
GO

UPDATE [SEBrowser.Widget] SET Name = 'RelayPerformance' WHERE Name = 'EventSearchRelayPerformance'
GO
UPDATE [SEBrowser.Widget] SET Type = 'RelayPerformance' WHERE Name = 'RelayPerformance'
GO

UPDATE [SEBrowser.Widget] SET Name = 'BreakerPerformance' WHERE Name = 'EventSearchBreakerPerformance'
GO
UPDATE [SEBrowser.Widget] SET Type = 'BreakerPerformance' WHERE Name = 'BreakerPerformance'
GO

UPDATE [SEBrowser.Widget] SET Name = 'CapBankAnalyticOverview' WHERE Name = 'EventSearchCapBankAnalyticOverview'
GO
UPDATE [SEBrowser.Widget] SET Type = 'CapBankAnalyticOverview' WHERE Name = 'CapBankAnalyticOverview'
GO